from sqlalchemy.orm import Session, joinedload
from models import *
from schema import *

# 查询所有的成绩
def get_stu_score_all(db: Session):
    return db.query(Stu_score_db).all()

# 根据学生姓名查询成绩
def get_stu_score_by_name(db: Session, stu_name:str):
    return db.query(Stu_score_db).options(joinedload(Stu_score_db.to_student)).join(Student_db).filter(Student_db.student_name == stu_name).all()

# 添加学生成绩
def add_stu_score(db: Session, stu_score:Stu_score):
    try:
        score_exist = db.query(Stu_score_db).filter(Stu_score_db.student_id == stu_score.student_id,
                                                    Stu_score_db.round == stu_score.round).first()
        if score_exist:
            return "学生成绩已经存在，不需要添加"
        else:
            score_db = Stu_score_db(**stu_score.dict())
            db.add(score_db)
            db.commit()
            db.refresh(score_db)
            return "学生成绩已经添加!"
    except Exception as e:
        return f"发生异常{e}"

# 更新学生成绩
def update_stu_score(db: Session, stu_score:Stu_score):
    score_exist = db.query(Stu_score_db).filter(Stu_score_db.student_id == stu_score.student_id,
                                                Stu_score_db.round == stu_score.round).first()
    try:
        if score_exist:
            db.query(Stu_score_db).filter(Stu_score_db.student_id == stu_score.student_id,
                                          Stu_score_db.round == stu_score.round).update(stu_score.dict())
            db.commit()
            return f"学号{stu_score.student_id}，考核序次{stu_score.round}更新成功!"
        else:
            return f"找不到学号{stu_score.student_id}，考核序次{stu_score.round}的成绩!"
    except Exception as e:
        return f"发生异常{e}"

# 删除学生成绩
def delete_stu_score(db: Session, stu_id:int,rnd:int):
    score_exist = db.query(Stu_score_db).filter(Stu_score_db.student_id == stu_id,
                                                Stu_score_db.round == rnd).first()
    if score_exist:
        db.query(Stu_score_db).filter(Stu_score_db.student_id == stu_id,Stu_score_db.round == rnd).delete()
        db.commit()
        return "删除成功!"
    else:
        return f"{stu_id}学生，考核序次{rnd}不存在!"


